package datasources.dbdatasource;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import org.jopac2.utils.BookSignature;

//import JOpac2.dataModules.RecordFactory;
//import JOpac2.dataModules.RecordInterface;

import bookhive.datasource.DataSource;
import bookhive.datastructures.datasource.DataItem;
import bookhive.datastructures.datasource.HashItem;

public class DerbyDataSource implements DataSource {

	private static final String ENCODING = "UTF-8";

	private Vector<HashItem> hashresult;

	private Vector<DataItem> dataresult;

	private Connection connection = null;

	public DerbyDataSource() {
		initConnection();
		initDB();
		hashresult = new Vector<HashItem>();
		dataresult = new Vector<DataItem>();
	}

	private void initDB() {

		String schema = "CREATE SCHEMA dbBookhive";
		String recordsTable = "CREATE TABLE dbBookhive.records (ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), hash VARCHAR(45) NOT NULL, data CLOB NOT NULL, type VARCHAR(45) NOT NULL, PRIMARY KEY(ID) )";
		String signsTable = "CREATE TABLE dbBookhive.signs (ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), hash VARCHAR(45) NOT NULL, LibName VARCHAR(100) NOT NULL DEFAULT '', LibCode VARCHAR(45) NOT NULL DEFAULT '', BookNum VARCHAR(45) NOT NULL DEFAULT '', BookLoc VARCHAR(45) NOT NULL DEFAULT '', Cons VARCHAR(45) NOT NULL DEFAULT '', PRIMARY KEY(ID) )";
		String index = "CREATE INDEX HashIndex on dbBookhive.signs (hash)";

		Statement st;
		try {
			st = connection.createStatement();

			try {
				st.execute(schema);
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			try {
				st.execute(recordsTable);
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			try {
				st.execute(signsTable);
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			try {
				st.execute(index);
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			try {
				st.execute("SET SCHEMA dbBookhive");
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			st.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private void initConnection() {

		try {
			String driverName = "org.apache.derby.jdbc.EmbeddedDriver"; // MySQL
			Class.forName(driverName);
			String mydatabase = "dbBookhive";
			String url = "jdbc:derby:" + mydatabase + ";create=true"; // a
			connection = DriverManager.getConnection(url);
		} catch (ClassNotFoundException e) {
		} catch (SQLException e) {
		}
	}

	public Vector<HashItem> getHashResult() {
		return hashresult;
	}

	public Vector<DataItem> getDataResult() {
		return dataresult;
	}

	public synchronized Vector<HashItem> sendHashQuery(String query) {

		hashresult.clear();

		try {
			query = URLDecoder.decode(query, ENCODING);
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}

		String[] querys = query.split("&");
		String attr = querys[0];
		String[] attrs = attr.split("=");

		String q = "Select * from signs where hash in (SELECT records.hash FROM records where LOWER(records.data) LIKE '%"
				+ attrs[1].toLowerCase() + "%')";

		try {

			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(q);
			while (rs.next()) {
				HashItem hi = new HashItem();
				hi.id = rs.getLong("ID");
				hi.hash = rs.getString("hash");
				String libname = rs.getString("LibName");
				String libcode = rs.getString("LibCode");
				String booknum = rs.getString("BookNum");
				String bookloc = rs.getString("BookLoc");
				String cons = rs.getString("Cons");
				BookSignature bs = new BookSignature(libcode, libname, booknum,
						bookloc, cons);
				hi.signs.add(bs);
				hashresult.add(hi);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return hashresult;

	}

	public synchronized Vector<DataItem> sendDataQuery(String hash) {

		dataresult.clear();

		String query = "select * from records where hash='" + hash + "'";

		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				DataItem di = new DataItem();
				di.id = rs.getLong("ID");
				di.type = rs.getString("type");
				di.data = rs.getString("data");
				dataresult.add(di);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return dataresult;
	}

	public synchronized boolean sendUpdateQuery(String queryData) {

		boolean ok = false;
		try {
			queryData = URLDecoder.decode(queryData, ENCODING);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

		try {
			PreparedStatement ps = connection
					.prepareStatement("insert into signs(hash,LibName,LibCode,BookNum,BookLoc,Cons) values(?,?,?,?,?,?)");

			String[] params = queryData.split("&");
			for (int i = 0; i < params.length; i++) {
				String[] param = params[i].split("=");

				if (param[0].equals("hash"))
					ps.setString(1, param[1]);
				else if (param[0].equals("LID"))
					ps.setString(3, param[1]);
				else if (param[0].equals("LNA"))
					ps.setString(2, param[1]);
				else if (param[0].equals("BNO"))
					ps.setString(4, param[1]);
				else if (param[0].equals("BLO"))
					ps.setString(5, param[1]);
				else if (param[0].equals("BCO"))
					ps.setString(6, param.length < 2 ? "" : param[1]);
			}

			ok = ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("Update queryData: " + queryData);
		return ok;
	}

	public synchronized boolean sendRedundQuery(String queryData) {
		boolean ok = false;
		try {
			queryData = URLDecoder.decode(queryData, ENCODING);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

		try {
			PreparedStatement ps = connection
					.prepareStatement("insert into records(hash,data,type) values(?,?,?)");

			String[] params = queryData.split("&");
			for (int i = 0; i < params.length; i++) {
				String[] param = params[i].split("=");

				if (param[0].equals("hash"))
					ps.setString(1, param[1]);
				else if (param[0].equals("data"))
					ps.setString(2, param[1]);
				else if (param[0].equals("type"))
					ps.setString(3, param[1]);
			}

			ok = ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("Update queryData: " + queryData);
		return ok;
	}

	public HashItem getRandomRecord() {
		HashItem ret = new HashItem();
		long max = getMaxID();
		long id = (long) (Math.random() * max);

		try {
			String q = "select * from records where id=" + id;
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(q);
			if (rs.next()) {
				ret.id = id;
				ret.hash = rs.getString("hash");
				ret.type = rs.getString("type");
			}
			rs.close();

			q = "select * from signs where hash='" + ret.hash + "'";
			rs = st.executeQuery(q);
			while (rs.next()) {
				BookSignature bs = new BookSignature(rs.getString("LibCode"),
						rs.getString("LibName"), rs.getString("BookNum"), rs
								.getString("BookLoc"), rs.getString("Cons"));
				ret.signs.add(bs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ret;
	}

	private long getMaxID() {
		long r = 0;
		String q = "select max(ID) from records";
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(q);
			if (rs.next())
				r = rs.getLong(1);
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return r;
	}

	// private boolean dbContains(BookSignature bs) {
	// boolean ret = false;
	// try {
	// String q = "select * from signs where LibName=? and LibCode=? and
	// BookNum=? and BookLoc=? and Cons=?";
	// PreparedStatement ps = connection.prepareStatement(q);
	// ps.setString(1, bs.getLibraryName());
	// ps.setString(2, bs.getLibraryId());
	// ps.setString(3, bs.getBookNumber());
	// ps.setString(4, bs.getBookLocalization());
	// ps.setString(5, bs.getBookCons());
	// ResultSet rs = ps.executeQuery();
	// if (rs.next())
	// ret = true;
	// rs.close();
	// ps.close();
	// } catch (Exception e) {
	// }
	// return ret;
	// }
	//
	// private synchronized RecordInterface getFromDb(String hash) {
	// RecordInterface ret = null;
	// Vector<DataItem> div = sendDataQuery(hash);
	// if (div.size() > 0) {
	// DataItem di = div.firstElement();
	// ret = RecordFactory.buildRecord(di.id, di.data, di.type, 0);
	// }
	// return ret;
	// }

}
